package com.css.bjdt_core.reportFill.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.cell.service.CustomSqlService;
import com.css.bjdt_core.reportFill.entity.RptCellCommentEntity;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.ReportConstant;
import com.css.bjdt_core.validation.service.ValidationInfoService;

@Service
public class RptCellCommentService extends CustomSqlService {
	@Autowired
	private Dao dao;
	@Autowired
	private ValidationInfoService validationInfoSvc;

	//根据年度ID，报表的实例ID和行列坐标，得到当前这个单元格的批注
	public RptCellCommentEntity getAnRptCellCommentEntity(String annualId, String dzzid, String absRowNum,
			String absColNum, String templateId) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("select ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT from " + tableName
				+ " where TEMPLATE_ID = @templateId"
				+ " and DZZ_ID = @dzzid and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum");
		strSql.params().set("templateId", templateId).set("absRowNum", absRowNum).set("absColNum", absColNum)
				.set("dzzid", dzzid);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
				while (rs.next()) {
					rptCellCommentEntity.setId(rs.getString("ID"));
					rptCellCommentEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellCommentEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
				}
				return rptCellCommentEntity;
			}
		});
		dao.execute(strSql);
		return strSql.getObject(RptCellCommentEntity.class);
	}

	//插入一条批注信息
	public void insertAnRptCellCommentEntity(String annualId, String dzzid, String absRowNum, String absColNum,
			String templateComment, String templateId) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql insertSql = Sqls.create("insert into " + tableName
				+ "(ID,DZZ_ID,TEMPLATE_ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT,SUM_STATUS) "
				+ " values(@id,@dzzid,@templateId,@absRowNum,@absColNum,@templateComment,@sum)");
		insertSql.params().set("id", CommonUtil.getUUID()).set("dzzid", dzzid).set("absRowNum", absRowNum)
				.set("absColNum", absColNum).set("templateComment", templateComment).set("templateId", templateId)
				.set("sum", "0");
		dao.execute(insertSql);
	}

	//判断是否有当前年度的审批表
	public void isExistCommentTable(String annualId) {
		String tableName = ReportConstant.COMMENT_TABLE_PREFIX + annualId;
		String count = validationInfoSvc.executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		//Mysql没有表返回null oracle返回"0"
		if (count == null || "0".equals(count)) {
			//if ("0".equals(count)) {
			validationInfoSvc.executeSql(ReportConstant.COMMENT_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//根据年度ID和报表的实例ID，获得本张报表的所有批注信息
	public List<RptCellCommentEntity> getAllTemplateComment(String annualId, String templateId, String dzzid) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("select ID,TEMPLATE_ID,ABS_ROW_NUM,ABS_COL_NUM,TEMPLATE_COMMENT from " + tableName
				+ " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzid");
		strSql.params().set("templateId", templateId).set("dzzid", dzzid);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setId(rs.getString("ID"));
					rptCellCommentEntity.setTemplateId(rs.getString("TEMPLATE_ID"));
					rptCellCommentEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellCommentEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(RptCellCommentEntity.class);
	}
	
	public List<RptCellCommentEntity> a_queryAllComment(String annualId, String templateId, String dzzId) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("select '"+ dzzId +"' DZZ_ID, a.TEMPLATE_ID, a.ABS_ROW_NUM, a.ABS_COL_NUM, LISTAGG(TO_CHAR(CONCAT(CONCAT(b.DZZJC,','),a.TEMPLATE_COMMENT)),';') within group (order by b.DZZJC) TEMPLATE_COMMENT "
				+ "from (select ID, DZZ_ID, TEMPLATE_ID, ABS_ROW_NUM, ABS_COL_NUM, TEMPLATE_COMMENT "
				+ "from "+ tableName +" "
				+ "where TEMPLATE_ID='"+ templateId +"') a "
				+ "inner join (select DZZID, DZZJC from T_M_ZZINFO "
				+ "where SJDZZID='"+ dzzId +"' and DELETEFLAG=0 "
				+ "order by ORDERNUM ) b "
				+ "on a.DZZ_ID = B.DZZID "
				+ "group by a.TEMPLATE_ID, a.ABS_ROW_NUM, a.ABS_COL_NUM");
//		strSql.params().set("templateId", templateId).set("dzzid", dzzid);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setDzzId(rs.getString("DZZ_ID"));
					rptCellCommentEntity.setTemplateId(rs.getString("TEMPLATE_ID"));
					rptCellCommentEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellCommentEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(RptCellCommentEntity.class);
	}
	
	//删除指定报表中的所有批注
	public void a_deleteCommentATemplate(String annualId, String dzzId, String templateId) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("delete from " + tableName + " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId");
		strSql.params().set("dzzId", dzzId).set("templateId", templateId);
		dao.execute(strSql);
	}

	//删除一条报表批注，根据年度ID、报表实例ID、行列坐标
	public void deleteAnTemplateComment(String annualId, String dzzid, String absRowNum, String absColNum,
			String templateId) {
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("delete from " + tableName + " where TEMPLATE_ID = @templateId"
				+ " and DZZ_ID = @dzzid and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum");
		strSql.params().set("dzzid", dzzid).set("absRowNum", absRowNum).set("absColNum", absColNum).set("templateId",
				templateId);
		dao.execute(strSql);
	}

	//根据传入得党组织ID，还有行列坐标，版本ID查出这个单位下一级单位得这个单元格的批注信息
	public List<RptCellCommentEntity> selectDzzCommenByTemplateIdColRow(String annualId, String dzzId, String absRowNum,
			String absColNum, final String templateId) {
		String dzzIdsSql = "select t.dzzId from T_M_ZZINFO t where t.sjdzzid = @dzzId";
		String tableCommentName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		String tableCellValueName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId.toUpperCase();
		Sql queryCommentSql = Sqls
				.create("select t1.ABS_ROW_NUM,t1.ABS_COL_NUM,t1.cell_value,t1.DZZ_ID,t2.TEMPLATE_COMMENT,t3.DZZMC from "
						+ tableCellValueName + " t1 left join " + tableCommentName
						+ " t2 on t1.template_id = t2.template_id and t1.dzz_id = t2.dzz_id and t1.abs_row_num = t2.abs_row_num and t1.abs_col_num = t2.abs_col_num "
						+ " left join T_M_ZZINFO t3 on t1.DZZ_ID = t3.DZZID "
						+ " where t1.template_id = @templateId and t1.abs_row_num = @absRowNum and t1.abs_col_num = @absColNum and t3.deleteflag = '0' "
						+ " and t1.dzz_id in (" + dzzIdsSql + ") ORDER BY t3.ORDERNUM ");
		queryCommentSql.params().set("templateId", templateId).set("absRowNum", absRowNum).set("absColNum", absColNum)
				.set("dzzId", dzzId);

		queryCommentSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellCommentEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellCommentEntity.setTemplateId(templateId);
					if (rs.getString("TEMPLATE_COMMENT") == null || rs.getString("TEMPLATE_COMMENT").equals("")) {
						rptCellCommentEntity.setTemplateComment("stringEmpty");
					} else {
						rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
					}
					rptCellCommentEntity.setCellValue(rs.getString("CELL_VALUE"));
					rptCellCommentEntity.setDzzName(rs.getString("DZZMC"));
					rptCellCommentEntity.setDzzId(rs.getString("DZZ_ID"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(queryCommentSql);
		return queryCommentSql.getList(RptCellCommentEntity.class);
	}
	
	/**
	 * get  List<RptCellCommentEntity> by annualId and dzzId
	 * @param annualId
	 * @param dzzId
	 * @return
	 */
	public List<RptCellCommentEntity> getADZzRptComment(String annualId, String dzzId){
		String tableName = "RPT_CELL_COMM_" + annualId.toUpperCase();
		Sql strSql = Sqls.create("select t.NAME as TEMPLATE_NAME,rc.REL_ROW_NUM,rc.REL_COL_NUM,com.TEMPLATE_COMMENT,com.TEMPLATE_ID from " + tableName
				+ " com  inner join RPT_CELL rc on com. TEMPLATE_ID= rc.TEMPLATE_ID and com.ABS_ROW_NUM=rc.ABS_ROW_NUM " +
				" and com.ABS_COL_NUM=rc.ABS_COL_NUM inner join RPT_TEMPLATE t "+
				" on com.TEMPLATE_ID=t.TEMPLATEID where com.DZZ_ID = @dzzid order by com.TEMPLATE_ID, rc.REL_COL_NUM,rc.REL_ROW_NUM");
		strSql.params().set("dzzid", dzzId);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<RptCellCommentEntity> rptCellCommentEntityList = new ArrayList<RptCellCommentEntity>();
				while (rs.next()) {
					RptCellCommentEntity rptCellCommentEntity = new RptCellCommentEntity();
					rptCellCommentEntity.setTemplateName(rs.getString("TEMPLATE_NAME"));
					rptCellCommentEntity.setRelRowNum(rs.getString("REL_ROW_NUM"));
					rptCellCommentEntity.setRelColNum(rs.getString("REL_COL_NUM"));
					rptCellCommentEntity.setTemplateComment(rs.getString("TEMPLATE_COMMENT"));
					rptCellCommentEntity.setTemplateId(rs.getString("TEMPLATE_ID"));
					rptCellCommentEntityList.add(rptCellCommentEntity);
				}
				return rptCellCommentEntityList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(RptCellCommentEntity.class);
		
	}
}
